CREATE DATABASE VIDEOS_FACTORY
GO

USE VIDEOS_FACTORY
GO

CREATE TABLE USER_PROFILE(
Profile_id uniqueidentifier Primary Key Default(newid()) NOT NULL,
UserId nvarchar(256) NOT NULL,
UserName nvarchar(256) NOT NULL,
IdNumber varchar(13),
FirstName varchar(20),
LastName varchar(30),
ContactNo varchar(10),
Physical_Address varchar(50))
GO

Create Proc Add_User_Profile
(
@UserId nvarchar(256),
@UserName nvarchar(256),
@IdNumber varchar(13),
@FirstName varchar(20),
@LastName varchar(30),
@ContactNo varchar(10),
@Physical_Address varchar(50)
)
As
Insert Into USER_PROFILE(UserId,UserName,IdNumber,FirstName,LastName,ContactNo,Physical_Address)
values(@UserId,@UserName,@IdNumber,@FirstName,@LastName,@ContactNo,@Physical_Address)
GO

Create Proc Get_User_Profile
As
Select UserId, UserName, IdNumber, FirstName, LastName, ContactNo, Physical_Address
from USER_PROFILE
Go

Create Proc Update_User_Profile
(
@UserId nvarchar(256),
@IdNumber varchar(13),
@FirstName varchar(20),
@LastName varchar(30),
@ContactNo varchar(10),
@Physical_Address varchar(50)
)
As
Update USER_PROFILE
SET IdNumber = @IdNumber,
FirstName = @FirstName,
LastName = @LastName,
ContactNo = @ContactNo,
Physical_Address = @Physical_Address
Where UserId = @UserId
GO

CREATE TABLE EQUIPMENT
(
EQUIPMENT_NUMBER VARCHAR(20) PRIMARY KEY NOT NULL,
EQUIPMENT_NAME VARCHAR(50) NOT NULL,
PRICE DECIMAL NOT NULL,
EQUIPMENT_DESCRIPTION VARCHAR(250) NOT NULL,
EQUIPMENT_IMAGE VARCHAR(100),
CATEGORY VARCHAR(20) NOT NULL
)
GO

CREATE PROCEDURE SAVE_EQUIPMENT
(
@EQUIPMENT_NUMBER VARCHAR(20),
@EQUIPMENT_NAME VARCHAR(50),
@PRICE DECIMAL,
@EQUIPMENT_DESCRIPTION VARCHAR(250),
@EQUIPMENT_IMAGE VARCHAR(100),
@CATEGORY VARCHAR(20)
)
AS
INSERT INTO EQUIPMENT (EQUIPMENT_NUMBER, EQUIPMENT_NAME,PRICE,EQUIPMENT_DESCRIPTION ,EQUIPMENT_IMAGE,CATEGORY)
VALUES (@EQUIPMENT_NUMBER, @EQUIPMENT_NAME,@PRICE,@EQUIPMENT_DESCRIPTION ,@EQUIPMENT_IMAGE,@CATEGORY)
GO

CREATE PROCEDURE UPDATE_EQUIPMENT
(
@EQUIPMENT_NUMBER VARCHAR(20),
@EQUIPMENT_NAME VARCHAR(50),
@PRICE DECIMAL,
@EQUIPMENT_DESCRIPTION VARCHAR(250),
@EQUIPMENT_IMAGE VARCHAR(100),
@CATEGORY VARCHAR(20)
)
AS
UPDATE EQUIPMENT
SET EQUIPMENT_NAME = @EQUIPMENT_NAME,
PRICE = @PRICE,
EQUIPMENT_DESCRIPTION = @EQUIPMENT_DESCRIPTION, 
EQUIPMENT_IMAGE = @EQUIPMENT_IMAGE,
CATEGORY = @CATEGORY
WHERE EQUIPMENT_NUMBER = @EQUIPMENT_NUMBER
GO

CREATE PROCEDURE DELETE_EQUIPMENT
(
@EQUIPMENT_NUMBER VARCHAR(20)
)
AS
DELETE EQUIPMENT
FROM EQUIPMENT
WHERE EQUIPMENT_NUMBER = @EQUIPMENT_NUMBER
GO

CREATE TABLE SHOPPING_CART(
SHOPPING_CART_ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT(NEWID()) NOT NULL,
USERID NVARCHAR(256) NOT NULL,
EQUIPMENT_ID VARCHAR(20) NOT NULL,
EQUIPMENT_NAME VARCHAR(50) NOT NULL,
PRICE DECIMAL(7,2) NOT NULL,
EQUIPMENT_IMAGE VARCHAR(100) NOT NULL,
QTY INT)
GO

CREATE PROCEDURE ADD_TO_CART
(
@USERID NVARCHAR(256),
@EQUIPMENT_ID VARCHAR(20),
@EQUIPMENT_NAME VARCHAR(50),
@PRICE DECIMAL(7,2),
@EQUIPMENT_IMAGE VARCHAR(100),
@QTY INT
)
AS
INSERT INTO SHOPPING_CART (USERID,EQUIPMENT_ID,EQUIPMENT_NAME,PRICE,EQUIPMENT_IMAGE, QTY)
VALUES (@USERID,@EQUIPMENT_ID,@EQUIPMENT_NAME,@PRICE,@EQUIPMENT_IMAGE,@QTY)
GO

CREATE PROCEDURE GET_CATEGORY
AS
SELECT CATEGORY
FROM EQUIPMENT
GO

CREATE PROCEDURE GET_EQUIPMENT
AS
SELECT * FROM EQUIPMENT
GO

Create Table Cart_Item(
itemid uniqueidentifier primary key default(newid()) NOT NULL,
EQUIPMENT_ID VARCHAR(20) NOT NULL,
USERID nvarchar(256) NOT NULL)
GO

Create proc Add_cart_item(
@EQUIPMENT_ID VARCHAR(20),
@USERID nvarchar(256)
)
as
Insert into Cart_Item(EQUIPMENT_ID,USERID)
values(@EQUIPMENT_ID,@USERID)
go

Create proc show_cart(
@userid nvarchar(256)
)
as
select equipment.equipment_number,equipment_name,price,equipment_image,cart_item.userid 
from equipment, cart_item
where equipment.EQUIPMENT_NUMBER = cart_item.equipment_id
and cart_item.userid = @userid
go

Create proc delete_cart_item(
@userid nvarchar(256),
@equipment_id varchar(20)
)
as
delete cart_item
from cart_item
where userid = @userid
and equipment_id = @equipment_id
go

select * from cart_item
go

drop table cart_item
go


Select * from USER_PROFILE
go

Select UserId, Email from aspnet_Membership
go

select * from shopping_cart
go

